const {
	insertSqlPublic,
	getUuid
} = require('./global')
const db = require('../db/index');


// 查询订单列表
exports.getList = async (req, res) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			const { status, keywords } = req.query;
			const params = req.query;
			let currentPage = params["currentPage"] || 1;
			let pageSize = params['pageSize'] || 10;
			// console.log(currentPage,  typeof pageSize)
			// 判断 status 是否存在
			let whereStatus = 'WHERE 1 = 1';
			let whereKeywords = '';
			let whereParams = [];
			if (status != 99) { //99是全部
				whereStatus = 'WHERE tb1.status = ?';
				whereParams.push(status);
			}
			if (keywords) {
				whereKeywords += ` AND (tb1.order_code LIKE '%${keywords}%' or tb3.name LIKE '%${keywords}%' or tb3.phone LIKE '%${keywords}%') `;
			}
			const joinUser = "LEFT JOIN cc_user tb2 on tb2.openid = tb1.openid"
			const joinClient = "LEFT JOIN cc_address tb3 on tb3.id = tb1.address_id "
			const joinStatus = "LEFT JOIN cc_order_status tb4 on tb4.status = tb1.status "
			const sql = `SELECT tb1.*, tb2.name userName, tb3.name clientName, tb3.phone clientPhone, tb3.city, tb3.detail, tb4.name statusName FROM cc_orders tb1 ${joinUser} ${joinClient} ${joinStatus} ${whereStatus} ${whereKeywords} ORDER BY tb1.ent_date desc LIMIT ? OFFSET ?`;
			// 分页查询出所有的订单
			// console.log(typeof pageSize, (currentPage - 1) * pageSize)
			const [rows] = await connection.execute(sql, [...whereParams, pageSize + "", (currentPage - 1) * pageSize + ""])
			console.log(rows)

			let orders = [];
			if (rows.length) {
				// 查询这些订单对应的商品
				const goodsSql = `SELECT tb1.order_id, tb1.quantity, tb2.id goodsId, tb2.name goodsName, tb2.price goodsPrice, tb2.specs_male, tb2.specs_female, tb2.mainPic FROM cc_order_goods tb1 LEFT JOIN cc_goods tb2 ON tb2.id = tb1.goods_id WHERE tb1.order_id IN (?);`;

				const orderIds = rows.map(row => row.id);
				const [goodsRows] = await connection.query(goodsSql, [orderIds]);

				// 合并相同订单的数据
				orders = rows.map(row => {
					const children = goodsRows.filter(goodsRow => goodsRow.order_id === row.id).map(goods => ({
						goodsId: goods.goodsId,
						goodsName: goods.goodsName,
						goodsPrice: goods.goodsPrice,
						quantity: goods.quantity,
						specs_male: goods.specs_male.split(','),
						specs_female: goods.specs_female.split(','),
						mainPic: goods.mainPic
					}))
					// console.log(`Children for order ${row.id}:`, children);  
					return {
						...row,
						children,
					};
				});
			}

			// 统计待发货和售后的 订单数量
			const unsendSql = ""

			const data = {};
			data.rows = orders
			// 计算订单总数
			const totalSql = `SELECT COUNT(DISTINCT tb1.id) as total FROM cc_orders tb1 LEFT JOIN cc_address tb3 on tb3.id = tb1.address_id ${joinStatus} ${whereStatus} ${whereKeywords}`;
			const [totalRows] = await connection.execute(totalSql, [status]);
			data.total = totalRows[0].total

			res.json({
				status: 200,
				message: '成功',
				data: data
			});
		} catch (e) {
			console.error('Query error:', e);
			res.status(500).json({
				status: 500,
				message: e.message
			});
		} finally {
			// 无论查询是否成功，都释放连接
			connection.release();
		}
	} catch (err) {
		console.error('Database connection error:', err);
		res.status(500).json({
			status: 500,
			message: '数据库连接错误'
		});
	}
}

//统计status
exports.countStatus = async (req, res) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			const sql = `
			SELECT 
				tb2.status,
				tb2.name,
				COUNT(tb1.id) as count
			FROM
				cc_order_status tb2
			LEFT JOIN 
				cc_orders tb1 ON tb1.status = tb2.status
			WHERE
				tb2.status <> 99
			GROUP BY 
				tb2.status, tb2.name
			ORDER BY
				CASE tb2.status
					WHEN 0 THEN 1
					WHEN 1 THEN 2
					WHEN 2 THEN 3
					WHEN -1 THEN 4
				END;
			`;
			const [rows] = await connection.execute(sql);
			res.json({
				status: 200,
				message: '成功',
				data: rows
			});
		} catch (e) {
			console.error('Query error:', e);
			res.status(500).json({
				status: 500,
				message: e.message
			});
		} finally {
			// 无论查询是否成功，都释放连接
			connection.release();
		}
	} catch (err) {
		console.error('Database connection error:', err);
		res.status(500).json({
			status: 500,
			message: '数据库连接错误'
		});
	}
}



//新增
exports.addOrder = async (req, res) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			const params = req.body;
			params.id = getUuid();
			// 开始事务
			await connection.beginTransaction();

			const sqlOrders = await insertSqlPublic('cc_orders', 'crab_card', params);
			// 执行 SQL 语句
			const [rowsOrders] = await connection.execute(sqlOrders);
			if (rowsOrders.affectedRows === 1) {
				const goodsData = params.goods_data;
				const sqlGoodsPromises = goodsData.map(good => {
					return insertSqlPublic('cc_order_goods', 'crab_card', {
						order_id: params.id,
						goods_id: good.id,
						quantity: good.quantity
					});
				});
				// 执行所有的插入操作
				const sqlGoods = await Promise.all(sqlGoodsPromises);
				const resultsGoods = await Promise.all(sqlGoods.map(sql => connection.execute(sql)));

				// 检查所有插入操作是否成功
				const allSuccess = resultsGoods.every(result => {
					// console.log(result[0].affectedRows, 'result[0]')
					return result[0].affectedRows === 1;
				});
				if (allSuccess) {
					// 如果所有插入操作都成功，提交事务
					await connection.commit();
					res.json({
						status: 200,
						message: '订单及其商品数据插入成功'
					});
				} else {
					// 如果有任何插入操作失败，回滚事务
					await connection.rollback();
					res.status(400).json({
						status: 400,
						message: '插入失败'
					});
				}

			} else {
				res.status(400).json({ // 使用 400 表示 Bad Request
					status: 400,
					message: '插入失败'
				});
			}
		} catch (e) {
			console.error('Query error:', e);
			res.status(500).json({
				status: 500,
				message: e.message
			});
		} finally {
			// 无论查询是否成功，都释放连接
			connection.release();
		}
	} catch (err) {
		console.error('Database connection error:', err);
		res.status(500).json({
			status: 500,
			message: '数据库连接错误'
		});
	}
};



//更新
exports.updateOrder = async (req, res) => {
	try {
		// 等待从连接池中获取一个连接
		const connection = await db.getConnection();
		try {
			const params = req.body;
			// 构建更新字段数组
			const updateFields = [];
			let values = [];
			// 映射字段
			const fields = {
				address_id: params.address_id,
				status: params.status,
				send_date: params.send_date,
				send_code: params.send_code,
			};
			// 遍历字段，构建更新字段数组
			Object.entries(fields).forEach(([key, value]) => {
				if (value !== undefined) {
					updateFields.push(`${key} = ?`);
					values.push(value);
				}
			});
			// console.log(params)
			const sql = `update cc_orders set ${updateFields.join(', ')} WHERE id = ?`;
			values.push(params.id);
			// 执行 SQL 语句
			const [rows] = await connection.execute(sql, values);
			if (rows.affectedRows === 1) {
				res.json({ // 使用 201 表示 Created
					status: 200,
					message: '修改成功',
					data: rows
				});
			} else {
				res.status(400).json({ // 使用 400 表示 Bad Request
					status: 400,
					message: '修改失败'
				});
			}
		} catch (e) {
			console.error('Query error:', e);
			res.status(500).json({
				status: 500,
				message: e.message
			});
		} finally {
			// 无论查询是否成功，都释放连接
			connection.release();
		}
	} catch (err) {
		console.error('Database connection error:', err);
		res.status(500).json({
			status: 500,
			message: '数据库连接错误'
		});
	}
};